# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# 设置输出文件夹路径
output_folder = r'output/21'
os.makedirs(output_folder, exist_ok=True)

# 加载数据（请替换为实际文件路径）
file_path = r'data/21/女装店铺客户画像绘制数据分析-原始数据.xlsx'

# 读取客户信息表
df_customers = pd.read_excel(file_path)

# 设置中文字体
font_path = r'fonts/SIMSUN.TTC'  # 请根据实际路径调整
font_prop = FontProperties(fname=font_path)

def save_analysis_to_excel(pivot_table, output_image_path, output_excel_path, sheet_title, conclusion_text):
    with pd.ExcelWriter(output_excel_path, engine='openpyxl') as writer:
        pivot_table.to_excel(writer, sheet_name=sheet_title)
        
        workbook = writer.book
        worksheet = writer.sheets[sheet_title]
        
        img = Image(output_image_path)
        worksheet.add_image(img, 'E2')

        analysis_sheet = workbook.create_sheet(title='分析结论')
        analysis_sheet.cell(row=1, column=1, value=conclusion_text)

# 第一题：客户年龄分组分析
age_bins = [0, 18, 25, 35, 45, 55, float('inf')]
age_labels = ['0-18', '18-25', '25-35', '35-45', '45-55', '55+']
df_customers['年龄分组'] = pd.cut(df_customers['客户年龄'], bins=age_bins, labels=age_labels, right=False)

pivot_table_age = pd.pivot_table(df_customers, values='客户昵称', index='年龄分组', aggfunc='count')
pivot_table_age.columns = ['客户数量']

plt.figure(figsize=(14, 7))
pivot_table_age.plot(kind='bar', legend=False)
plt.xlabel('年龄分组', fontproperties=font_prop)
plt.ylabel('客户数量', fontproperties=font_prop)
plt.title('客户年龄分组分析', fontproperties=font_prop)
plt.xticks(fontproperties=font_prop, rotation=45)
plt.tight_layout()

output_image_path_age = os.path.join(output_folder, 'customer_age_distribution.png')
plt.savefig(output_image_path_age, dpi=300, bbox_inches='tight')
plt.close()

output_excel_path_age = os.path.join(output_folder, '客户年龄分组分析.xlsx')
save_analysis_to_excel(
    pivot_table_age,
    output_image_path_age,
    output_excel_path_age,
    '客户年龄分组分析',
    "客户年龄分组分析已完成。"
)

print("客户年龄分组分析已完成，结果已保存到", output_excel_path_age)

# 第二题：客户端来源分析
pivot_table_client = pd.pivot_table(df_customers, values='客户昵称', index='客户端', aggfunc='count')
pivot_table_client.columns = ['客户数量']
client_percentage = (pivot_table_client / pivot_table_client.sum() * 100).round(2)

plt.figure(figsize=(8, 8))
plt.pie(client_percentage['客户数量'], labels=client_percentage.index, autopct='%1.2f%%', startangle=90, textprops={'fontproperties': font_prop})
plt.title('客户端来源分析', fontproperties=font_prop)
plt.axis('equal')

output_image_path_client = os.path.join(output_folder, 'client_source_distribution.png')
plt.savefig(output_image_path_client, dpi=300, bbox_inches='tight')
plt.close()

mobile_percentage = client_percentage.loc['移动端', '客户数量'] if '移动端' in client_percentage.index else 0
pc_percentage = client_percentage.loc['PC端', '客户数量'] if 'PC端' in client_percentage.index else 0
print(f"该网店移动端客户占比是 {mobile_percentage:.2f}%，PC端客户占比是 {pc_percentage:.2f}%。")

output_excel_path_client = os.path.join(output_folder, '客户端来源分析.xlsx')
save_analysis_to_excel(
    client_percentage,
    output_image_path_client,
    output_excel_path_client,
    '客户端来源分析',
    f"该网店移动端客户占比是 {mobile_percentage:.2f}%，PC端客户占比是 {pc_percentage:.2f}%。"
)

print("客户端来源分析已完成，结果已保存到", output_excel_path_client)

# 第四题：客户购物偏好分析
pivot_table_product = pd.pivot_table(df_customers, values='客户昵称', index='产品名称', aggfunc='count')
pivot_table_product.columns = ['客户数量']
pivot_table_product = pivot_table_product.sort_values(by='客户数量', ascending=False)

plt.figure(figsize=(14, 7))
pivot_table_product.plot(kind='bar', legend=False)
plt.xlabel('产品名称', fontproperties=font_prop)
plt.ylabel('客户数量', fontproperties=font_prop)
plt.title('客户购物偏好分析', fontproperties=font_prop)
plt.xticks(fontproperties=font_prop, rotation=45)
plt.tight_layout()

output_image_path_product = os.path.join(output_folder, 'customer_product_preference.png')
plt.savefig(output_image_path_product, dpi=300, bbox_inches='tight')
plt.close()

top_product = pivot_table_product.index[0] if not pivot_table_product.empty else None
fourth_product = pivot_table_product.index[3] if len(pivot_table_product) > 3 else None
print(f"该网店客户偏好购买的产品，排名第一的是 {top_product}，排名第四的是 {fourth_product}。")

output_excel_path_product = os.path.join(output_folder, '客户购物偏好分析.xlsx')
save_analysis_to_excel(
    pivot_table_product,
    output_image_path_product,
    output_excel_path_product,
    '客户购物偏好分析',
    f"该网店客户偏好购买的产品，排名第一的是 {top_product}，排名第四的是 {fourth_product}。"
)

print("客户购物偏好分析已完成，结果已保存到", output_excel_path_product)